-- Agent 0 messages
CREATE EXTENSION IF NOT EXISTS pgcrypto;

create table messages (
  id uuid not null default gen_random_uuid (),
  created_at timestamp with time zone null default CURRENT_TIMESTAMP,
  session_id text not null,
  message jsonb not null,
  constraint messages_pkey primary key (id)
) TABLESPACE pg_default;

create index IF not exists idx_messages_session_id on messages using btree (session_id) TABLESPACE pg_default;
create index IF not exists idx_messages_created_at on messages using btree (created_at) TABLESPACE pg_default;
alter publication supabase_realtime add table messages;

-- RAG knowledge
create extension if not exists vector;

create table streamer_knowledge (
  id bigserial not null,
  session_id text not null,
  file_name character varying not null,
  chunk_number integer not null,
  title character varying not null,
  summary character varying not null,
  content text not null,
  embedding vector(768),
  created_at timestamp with time zone not null default timezone ('utc'::text, now()),
  constraint streamer_knowledge_pkey primary key (id),
  constraint streamer_knowledge_session_id_chunk_number_key unique (session_id, chunk_number)
) TABLESPACE pg_default;

create index IF not exists streamer_knowledge_embedding_idx on streamer_knowledge using ivfflat (embedding vector_cosine_ops) TABLESPACE pg_default;


-- Create a function to search for documentation chunks
create function match_streamer_knowledge (
  query_embedding vector(768),
  user_session_id text,
  match_count int default 5
) returns table (
  id bigint,
  session_id text,
  chunk_number integer,
  title varchar,
  summary varchar,
  content text,
  similarity float
)
language plpgsql
as $$
#variable_conflict use_column
begin
  return query
  select
    id,
    session_id,
    chunk_number,
    title,
    summary,
    content,
    1 - (streamer_knowledge.embedding <=> query_embedding) as similarity
  from streamer_knowledge
  where user_session_id = session_id
  order by streamer_knowledge.embedding <=> query_embedding
  limit match_count;
end;
$$;


-- YouTube Sessionstream
create table youtube_streams (
  id bigint generated by default as identity not null,
  created_at timestamp with time zone not null default now(),
  is_active smallint not null default '1'::smallint,
  session_id text not null,
  video_id text not null,
  live_chat_id text not null,
  next_chat_page character varying not null default ''::character varying,
  constraint youtube_streams_pkey primary key (id)
) TABLESPACE pg_default;

create index IF not exists idx_youtube_streams_session_id on youtube_streams using btree (session_id) TABLESPACE pg_default;
create index IF not exists idx_youtube_streams_live_chat_id on youtube_streams using btree (live_chat_id) TABLESPACE pg_default;
create index IF not exists idx_youtube_streams_created_at on youtube_streams using btree (created_at) TABLESPACE pg_default;


-- YouTube Live Chat Buzz
create table youtube_buzz (
  id bigint generated by default as identity not null,
  created_at timestamp with time zone not null default now(),
  buzz_type text not null,
  session_id text not null,
  original_chat text not null,
  author text not null,
  generated_response text not null,
  buzz_status smallint not null default '0'::smallint,
  constraint youtube_buzz_pkey primary key (id)
) TABLESPACE pg_default;

create index IF not exists idx_youtube_buzz_session_id on youtube_buzz using btree (session_id) TABLESPACE pg_default;
create index IF not exists idx_youtube_buzz_created_at on youtube_buzz using btree (created_at) TABLESPACE pg_default;


-- Streamer Replies
create table youtube_reply (
  id bigint generated by default as identity not null,
  created_at timestamp with time zone not null default now(),
  session_id text null,
  live_chat_id text null,
  retry_count smallint null default '0'::smallint,
  reply text null,
  is_written smallint null default '0'::smallint,
  constraint youtube_reply_pkey primary key (id)
) TABLESPACE pg_default;

create index IF not exists idx_youtube_reply_session_id on youtube_reply using btree (session_id) TABLESPACE pg_default;
create index IF not exists idx_youtube_reply_live_chat_id on youtube_reply using btree (live_chat_id) TABLESPACE pg_default;
create index IF not exists idx_youtube_reply_created_at on youtube_reply using btree (created_at) TABLESPACE pg_default;